package com.website.config.poi;

import com.website.system.model.FlowerType;
import com.website.system.model.Flowers;
import com.website.system.model.User;
import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by sang on 2019/8/1.
 */
public class PoiUtils {

    public static ResponseEntity<byte[]> exportFowers2Excel(List<Flowers> flowers) {
        HttpHeaders headers = null;
        ByteArrayOutputStream baos = null;
        try {
            //1.创建Excel文档
            HSSFWorkbook workbook = new HSSFWorkbook();
            //2.创建文档摘要
            workbook.createInformationProperties();
            //3.获取文档信息，并配置
            DocumentSummaryInformation dsi = workbook.getDocumentSummaryInformation();
            //3.1文档类别
            dsi.setCategory("花卉信息");
            //3.2设置文档管理员
            dsi.setManager("鸢尾");
            //3.3设置组织机构
            dsi.setCompany("庞庞集团");
            //4.获取摘要信息并配置
            SummaryInformation si = workbook.getSummaryInformation();
            //4.1设置文档主题
            si.setSubject("花卉信息表");
            //4.2.设置文档标题
            si.setTitle("花卉信息");
            //4.3 设置文档作者
            si.setAuthor("XIAOHONG");
            //4.4设置文档备注
            si.setComments("备注信息暂无");
            //创建Excel表单
            HSSFSheet sheet = workbook.createSheet("庞庞集团花卉花木信息表");
            //创建日期显示格式
            HSSFCellStyle dateCellStyle = workbook.createCellStyle();
            dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
            //创建标题的显示样式
            HSSFCellStyle headerStyle = workbook.createCellStyle();
            headerStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
            headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            //定义列的宽度
            sheet.setColumnWidth(0, 5 * 256);
            sheet.setColumnWidth(1, 12 * 256);
            sheet.setColumnWidth(2, 10 * 256);
            sheet.setColumnWidth(3, 5 * 256);
            sheet.setColumnWidth(4, 12 * 256);
            sheet.setColumnWidth(5, 20 * 256);
            sheet.setColumnWidth(6, 10 * 256);
            sheet.setColumnWidth(7, 10 * 256);
            //5.设置表头
            HSSFRow headerRow = sheet.createRow(0);
            HSSFCell cell0 = headerRow.createCell(0);
            cell0.setCellValue("花卉编号");
            cell0.setCellStyle(headerStyle);
            HSSFCell cell1 = headerRow.createCell(1);
            cell1.setCellValue("花卉名称");
            cell1.setCellStyle(headerStyle);
            HSSFCell cell2 = headerRow.createCell(2);
            cell2.setCellValue("花卉颜色");
            cell2.setCellStyle(headerStyle);
            HSSFCell cell3 = headerRow.createCell(3);
            cell3.setCellValue("花卉属性");
            cell3.setCellStyle(headerStyle);
            HSSFCell cell4 = headerRow.createCell(4);
            cell4.setCellValue("花卉详情");
            cell4.setCellStyle(headerStyle);
            HSSFCell cell5 = headerRow.createCell(5);
            cell5.setCellValue("花卉图片");
            cell5.setCellStyle(headerStyle);
            HSSFCell cell6 = headerRow.createCell(6);
            cell6.setCellValue("创建时间");
            cell6.setCellStyle(headerStyle);
            HSSFCell cell7 = headerRow.createCell(7);
            cell7.setCellValue("花卉类型");
            cell7.setCellStyle(headerStyle);
            //6.装数据
            for (int i = 0; i < flowers.size(); i++) {
                HSSFRow row = sheet.createRow(i + 1);
                Flowers f = flowers.get(i);
                row.createCell(0).setCellValue(f.getId());
                row.createCell(1).setCellValue(f.getName());
                row.createCell(2).setCellValue(f.getColor());
                row.createCell(3).setCellValue(f.getFproperty());
                row.createCell(4).setCellValue(f.getDetail());
                row.createCell(5).setCellValue(f.getImage());
                HSSFCell beginDateCell = row.createCell(6);
                beginDateCell.setCellValue(f.getCreatTime());
                beginDateCell.setCellStyle(dateCellStyle);
                row.createCell(7).setCellValue(f.getFid());

            }
            headers = new HttpHeaders();
            headers.setContentDispositionFormData("attachment",
                    new String("花卉花木表.xls".getBytes("UTF-8"), "iso-8859-1"));
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            baos = new ByteArrayOutputStream();
            workbook.write(baos);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return new ResponseEntity<byte[]>(baos.toByteArray(), headers, HttpStatus.CREATED);
    }

    public static List<Flowers> importFlowersList(MultipartFile file) {
        List<Flowers> flowers = new ArrayList<>();
        try {
            //xls格式的工作簿
            HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(file.getInputStream()));
            //工作表
            int numberOfSheets = workbook.getNumberOfSheets();

            for (int i = 0; i < numberOfSheets; i++) {
                //获取到第一个工作表
                HSSFSheet sheet = workbook.getSheetAt(i);
                //行数
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();

                Flowers f;
                for (int j = 0; j < physicalNumberOfRows; j++) {
                    if (j == 0) {
                        continue;//标题行
                    }
                    HSSFRow row = sheet.getRow(j);
                    if (row == null) {
                        continue;//没数据
                    }
                    //获取单元格列数
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();

                    f = new Flowers();
                    for (int k = 0; k < physicalNumberOfCells; k++) {
                        //单元格（第k列）
                        HSSFCell cell = row.getCell(k);

                        switch (cell.getCellTypeEnum()) {
                            case STRING: {
                                String cellValue = cell.getStringCellValue();
                                if (cellValue == null) {
                                    cellValue = "";
                                }
                                switch (k) {
                                    case 1:
                                        f.setName(cellValue);
                                        break;
                                    case 2:
                                        f.setColor(cellValue);
                                        break;
                                    case 3:
                                        f.setFproperty(cellValue);
                                        break;
                                    case 4:
                                        f.setDetail(cellValue);
                                        break;
                                    case 5:
                                        f.setImage(cellValue);
                                        break;
                                    case 6:
                                        f.setCreatTime(cellValue);
                                        break;
                                }
                            }
                            break;
                            default: {
                                switch (k) {
                                    case 7:
                                        f.setFid((int) cell.getNumericCellValue());
                                        break;

                                }
                            }

                        }
                    }
                    flowers.add(f);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return flowers;
    }

}
